# coding=utf-8
import xlrd
import xlsxwriter as xw


def one_to_many():
    path = "./mapper.xlsx"
    fileName = 'get.xlsx'
    excel = xlrd.open_workbook(path, encoding_override='utf-8')
    sheet = excel.sheet_by_index(0)
    list = []
    for row in range(1, sheet.nrows):
        m = sheet.cell_value(row, 0)
        # print(m + "\n")
        if '\n' in m:
            mm = m.split('\n')
            for a in mm:
                if a not in list:
                    if " " in a:
                        a = a.replace(" ", "")
                    if ";" in a:
                        a = a.replace(";", "")
                    list.append(a)
        else:
            if m not in list:
                list.append(m)
    # m = sheet.cell_value(1, 0)
    workbook = xw.Workbook(fileName)  # 创建工作簿
    worksheet1 = workbook.add_worksheet("sheet1")  # 创建子表
    worksheet1.activate()  # 激活表
    i = 1  # 从第二行开始写入数据
    for j in range(len(list)):
        insertData = list[j]
        print(insertData)
        worksheet1.write(i, 0, insertData)
        i += 1
    workbook.close()  # 关闭表
    print(list)
    print(len(list))
if __name__ == "__main__":
    one_to_many()